package com.huabo.contract.oracle.mapper;

import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblContractCollection;
import com.huabo.contract.oracle.entity.TblContractInvoicesmanagemen;
import com.huabo.contract.oracle.entity.TblContractPlannode;
import com.huabo.contract.oracle.entity.TblCyhwUnit;

import lombok.Data;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fasterxml.jackson.annotation.JsonFormat;

import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author huabo
 * @since 2022-03-11
 */
public interface TblContractCollectionMapper extends BaseMapper<TblContractCollection> {


    @SelectProvider(type=TblContractCollectionMapperSqlConfig.class,method="selectListByPageInfo")
    List<TblContractCollection> selectListByPageInfo(PageInfo<TblContractCollection> pageInfo, String staffid, String contractname, String contractno,BigDecimal pid, TblContractCollection collection);

    @SelectProvider(type=TblContractCollectionMapperSqlConfig.class,method="selectCountByPageInfo")
    Integer selectCountByPageInfo(String staffid, String contractname, String contractno,BigDecimal pid, TblContractCollection collection);

    @Select("SELECT * FROM TBL_CONTRACT_COLLECTION WHERE COLLECTIONID = #{nodeId}")
    @Options(useGeneratedKeys=true, keyProperty="collectionid", keyColumn="COLLECTIONID")
    Integer checkMoneyNodeId(BigDecimal nodeId);

    @Select("SELECT TCP.NODEPOST*TCU.CONTRACTMONEY/100 AS SKMONEY FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID LEFT JOIN TBL_CONTRACT_COLLECTION TCC ON TCP.NODEID = TCC.NODEID LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCI.INVOICEID = TCC.INVOICEID WHERE TCP.NODEID = #{nodeId} GROUP BY TCP.NODEPOST*TCU.CONTRACTMONEY")
    @Options(useGeneratedKeys=true, keyProperty="collectionid", keyColumn="COLLECTIONID")
    Integer getSkMoneyNodeId(BigDecimal nodeId);

    @Select("SELECT * FROM TBL_CONTRACT_COLLECTION WHERE COLLECTIONID = #{contractid}")
    TblContractCollection findByContractId(BigDecimal contractid);

    @UpdateProvider(type=TblContractCollectionMapperSqlConfig.class,method="UpdateOldCollection")
    void UpdateOldCollection(TblContractCollection oldCollection);

    @InsertProvider(method="SaveMengerCollectionEntity",type=TblContractCollectionMapperSqlConfig.class)
    @Options(useGeneratedKeys=true, keyProperty="collectionid", keyColumn="COLLECTIONID")
    void SaveMengerCollectionEntity(TblContractCollection collection);

    @Select("SELECT * FROM TBL_CONTRACT_COLLECTION TCC \n" +
            "LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID = TCI.INVOICEID\n" +
            "LEFT JOIN TBL_CYHW_UNIT TCU ON TCC.CONTRACTID = TCU.CONTRACTID \n" +
            "LEFT JOIN TBL_FLOW TF ON TCU.FLOWID = TF.FLOWID\n" +
            "LEFT JOIN TBL_CYHW_PROJECTBUDGET TCP ON TCU.CONTRACTXDFXINFO = TCP.BUDGETID \n" +
            "LEFT JOIN TBL_CONTRACT_PLANNODE TCP ON TCC.NODEID = TCP.NODEID\n" +
            "LEFT JOIN TBL_COUNTERPART_BANKINFO TCB ON TCC.COUNTERBANK = TCB.BANKID\n" +
            "LEFT JOIN TBL_ORG_BANKACCOUNT TOB ON TCC.ORGBANK = TOB.BANKID \n" +
            "WHERE TCC.COLLECTIONID = #{collectionId}")
    //@Options(useGeneratedKeys=true, keyProperty="collectionid", keyColumn="COLLECTIONID")
    TblContractCollection findByCollectionId(Integer collectionId);

    @Delete("DELETE FROM TBL_CONTRACT_COLLECTION WHERE COLLECTIONID = #{collectionId}")
    void removeContractCollection(Integer collectionId);

//    @Select("SELECT * FROM TBL_CONTRACT_COLLECTION TCC \n" +
//            "LEFT JOIN TBL_CYHW_UNIT TCU ON TCC.CONTRACTID = TCU.CONTRACTID \n" +
//            "LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCI.INVOICEID = TCC.INVOICEID \n" +
//            "LEFT JOIN TBL_CONTRACT_PLANNODE TCP ON TCC.NODEID = TCP.NODEID \n" +
//            "WHERE COLLECTIONID = #{collectionId}")
@Select("SELECT * FROM TBL_CONTRACT_COLLECTION TCC \n" +
        "LEFT JOIN TBL_CYHW_UNIT TCU  ON TCC.CONTRACTID = TCU.CONTRACTID\n" +
        "LEFT JOIN TBL_CONTRACT_PLANNODE TCP ON TCC.NODEID = TCP.NODEID\n" +
        "LEFT JOIN TBL_COUNTERPART_BANKINFO TCB ON TCC.COUNTERBANK = TCB.BANKID\n" +
        "LEFT JOIN TBL_ORG_BANKACCOUNT TOB ON TCC.ORGBANK = TOB.BANKID\n" +
        "LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID = TCI.INVOICEID\n" +
        "WHERE COLLECTIONID  = #{collectionId}")
    TblContractCollection findCollectionInfoByCollectId(Integer collectionId);

    @Select("SELECT TCC.COLLECTIONID,TCC.COLLECTIONSKDATE,TCC.COLLECTIONORGNAME,TCI.INVOICEMONEY,TCI.INVOICEHEADTEXT,TCI.INVOICENO FROM TBL_CONTRACT_COLLECTION TCC LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID = TCI.INVOICEID WHERE TCC.CONTRACTID = #{contractid} ORDER BY COLLECTIONID DESC")
    List<TblContractCollection> findcollectionListByContractId(BigDecimal contractid);
    
    
    @Select("SELECT WM_CONCAT(CONTRACTID) FROM TBL_CYHW_UNIT WHERE RECORDTYPE IN ('HTGL002','HTGL005')  AND CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE RECORDTYPE = 'HTGL005' AND RECORDPARENT IS NOT NULL) AND CONTRACTSTATUS = 7")
     String  getSkContractIds();
    
    @Select("SELECT * FROM TBL_CONTRACT_COLLECTION WHERE Contractid = #{contractid}  and nodeid=#{nodeid}" )
    TblContractCollection findByContract(String contractid,String nodeid);

}
